package com.jwiki.app.jtoolkit.common.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashSet;
import java.util.Properties;
import java.util.ServiceLoader;
import java.util.Set;

public class JdbcFactory {

    private static ThreadLocal<Connection> localConnection = new ThreadLocal<>();
    
    private Properties config = new Properties();
    
    public void setConnectConfig(String url, String user, String password) {
        config.put("url", url);
        config.put("user", user);
        config.put("password", password);
        config.setProperty("remarks", "true");
        config.setProperty("useInformationSchema", "true");
    }

    public Connection getConnection() throws SQLException {
        if (localConnection.get() == null) {
            ServiceLoader<Driver> drivers = ServiceLoader.load(Driver.class);
            if (drivers.iterator().hasNext()) {
                return drivers.iterator().next().connect(config.getProperty("url"), config);
            }
            throw new SQLException(Driver.class.getName() + " must not be null");
        } else {
            return localConnection.get();
        }
    }

    public void closeConnection() {
        if (localConnection.get() != null) {
            try {
                localConnection.get().close();
            } catch (SQLException e) {
                // ignored
            }
            localConnection.remove();
        }
    }

    public Set<PrimaryKey> getPrimaryKey(String table) throws SQLException {
        Set<PrimaryKey> primaryKeys = null;
        Connection connection = getConnection();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getPrimaryKeys(connection.getCatalog(), connection.getSchema(), table);
            primaryKeys = new LinkedHashSet<>();
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                PrimaryKey primaryKey = new PrimaryKey(columnName);
                Column column = getColumn(table, columnName);
                if (column != null) {
                    primaryKey.setNullable(column.isNullable());
                    primaryKey.setGenerated(column.isGenerated());
                    primaryKey.setRemarks(column.getRemarks());
                    primaryKey.setDefaults(column.getDefaults());
                    primaryKey.setJdbcType(column.getJdbcType());
                }
                primaryKeys.add(primaryKey);
            }
        } finally {
            closeConnection();
        }
        return primaryKeys;
    }

    public Column getColumn(String table, String column) throws SQLException {
        Connection connection = getConnection();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getColumns(connection.getCatalog(), connection.getSchema(), table, column);
            if (rs.next()) {
                Column newColumn = new Column(rs.getString("COLUMN_NAME"));
                newColumn.setNullable("YES".equals(rs.getString("IS_NULLABLE")));
                newColumn.setGenerated("YES".equals(rs.getString("IS_AUTOINCREMENT")));
                newColumn.setRemarks(rs.getString("REMARKS"));
                newColumn.setDefaults(rs.getString("COLUMN_DEF"));
                newColumn.setJdbcType(rs.getString("TYPE_NAME"));
                return newColumn;
            }
        } finally {
            closeConnection();
        }
        return null;
    }

    public Set<Column> getColumns(String table) throws SQLException {
        Set<Column> columns = null;
        Connection connection = getConnection();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getColumns(connection.getCatalog(), connection.getSchema(), table, "%");
            columns = new LinkedHashSet<>();
            while (rs.next()) {
                Column column = new Column(rs.getString("COLUMN_NAME"));
                column.setNullable("YES".equals(rs.getString("IS_NULLABLE")));
                column.setGenerated("YES".equals(rs.getString("IS_AUTOINCREMENT")));
                column.setRemarks(rs.getString("REMARKS"));
                column.setDefaults(rs.getString("COLUMN_DEF"));
                column.setJdbcType(rs.getString("TYPE_NAME"));
                columns.add(column);
            }
        } finally {
            closeConnection();
        }
        return columns;
    }

    public Set<Table> getTables() throws SQLException {
        Set<Table> tables = null;
        Connection connection = getConnection();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(connection.getCatalog(), connection.getSchema(), "%",
                    new String[] { "TABLE" });
            tables = new LinkedHashSet<>();
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                Table table = new Table(tableName);
                table.setRemarks(rs.getString("REMARKS"));
                table.setPrimaryKeys(getPrimaryKey(tableName));
                table.setColumns(getColumns(tableName));
                tables.add(table);
            }
        } finally {
            closeConnection();
        }
        return tables;
    }

    public Table getTable(String table) throws SQLException {
        Connection connection = getConnection();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(connection.getCatalog(), connection.getSchema(), table,
                    new String[] { "TABLE" });
            if (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                Table newTable = new Table(tableName);
                newTable.setRemarks(rs.getString("REMARKS"));
                newTable.setPrimaryKeys(getPrimaryKey(tableName));
                newTable.setColumns(getColumns(tableName));
                return newTable;
            }
        } finally {
            closeConnection();
        }
        return null;
    }

}
